//引入express第三方模块
const express = require("express");
//创建路由器对象
const r = express.Router();
//引入连接数据库,不是中间件不能写在app.js中
const pool = require("../mysql.js");
const fs = require("fs");
const path = require("path");

//从题库随机取出一百道不重复的题
r.get("/select", (req, res, next) => {
  pool.query("select * from tk", (err, result) => {
    if (err) {
      next(err);
      return;
    }
    if (result.length == 0) {
      res.send({
        status: 201,
        msg: "查询失败",
      });
    } else {
      //从一个给定的数组arr中,随机返回num个不重复项
      function getArrayItems(arr, num) {
        //新建一个数组,将传入的数组复制过来,用于运算,而不要直接操作传入的数组;
        var temp_array = new Array();
        for (var index in arr) {
          temp_array.push(arr[index]);
        }
        //取出的数值项,保存在此数组
        var return_array = new Array();
        for (var i = 0; i < num; i++) {
          //判断如果数组还有可以取出的元素,以防下标越界
          if (temp_array.length > 0) {
            //在数组中产生一个随机索引
            var arrIndex = Math.floor(Math.random() * temp_array.length);
            //将此随机索引的对应的数组元素值复制出来
            return_array[i] = temp_array[arrIndex];
            //然后删掉此索引的数组元素,这时候temp_array变为新的数组
            temp_array.splice(arrIndex, 1);
          } else {
            //数组中数据项取完后,退出循环,比如数组本来只有10项,但要求取出20项.
            break;
          }
        }
        return return_array;
      }

      var total = getArrayItems(result, 100);
      res.send({
        status: 200,
        msg: "查询成功",
        data: total,
      });
    }
    pool.releaseConnection(pool);
  });
});

//接收用户传入的数据
r.post("/receive", (req, res, next) => {
  //console.log(req.body);
  var arr = req.body.studentAnswer; //用户选择的答案
  var arr1 = req.body.timu; //题目
  var arr2 = req.body.answer; //正确答案
  var timestamp = Date.parse(new Date()); //获取当前时间
  var name = timestamp.toString(); //转字符串
  //   console.log(arr, arr1, arr2);
  //fs.writeFileSync(filename, data, [options]) data{String|Buffer}
  //如果不存在该用户名的文件，则创建
  if (
    !fs.existsSync(
      path.join(
        __dirname,
        "../public/score/" + req.cookies.userinfo.username + ".txt"
      )
    )
  ) {
    fs.writeFileSync(
      path.join(
        __dirname,
        "../public/score/" + req.cookies.userinfo.username + ".txt"
      ),
      "你的成绩" + req.body.score + "\n"
    );
    //追加内容
    for (var j = 0; j < arr.length; j++) {
      fs.appendFileSync(
        path.join(
          __dirname,
          "../public/score/" + req.cookies.userinfo.username + ".txt"
        ),
        "题目" + `${j + 1}` + arr1[j] + "\n"
      );
      fs.appendFileSync(
        path.join(
          __dirname,
          "../public/score/" + req.cookies.userinfo.username + ".txt"
        ),
        "你选择的答案" + arr[j] + "\n"
      );
      fs.appendFileSync(
        path.join(
          __dirname,
          "../public/score/" + req.cookies.userinfo.username + ".txt"
        ),
        "正确答案" + arr2[j] + "\n"
      );
    }
    let address = req.cookies.userinfo.username + ".txt";
    let myDate = new Date();
    pool.query(
      "insert into file (filename,score,time,name) values (?,?,?,?)",
      [address, req.body.score, myDate, req.cookies.userinfo.username],
      (err, result) => {
        //err可能产生的错误,如果出现错误
        if (err) {
          next(err); //抛给下一个中间件来处理
          //阻止往后执行
          return;
        }
        console.log("成功");
      }
    );
    pool.releaseConnection(pool);
  }

  //如果存在该用户名的文件，则再创建用户名+时间戳的文件
  else if (
    fs.existsSync(
      path.join(
        __dirname,
        "../public/score/" + req.cookies.userinfo.username + ".txt"
      )
    )
  ) {
    fs.writeFileSync(
      path.join(
        __dirname,
        "../public/score/" + req.cookies.userinfo.username + name + ".txt"
      ),
      "你的成绩" + req.body.score + "\n"
    );
    //追加内容
    for (var j = 0; j < arr.length; j++) {
      fs.appendFileSync(
        path.join(
          __dirname,
          "../public/score/" + req.cookies.userinfo.username + name + ".txt"
        ),
        "题目" + `${j + 1}` + arr1[j] + "\n"
      );
      fs.appendFileSync(
        path.join(
          __dirname,
          "../public/score/" + req.cookies.userinfo.username + name + ".txt"
        ),
        "你选择的答案" + arr[j] + "\n"
      );
      fs.appendFileSync(
        path.join(
          __dirname,
          "../public/score/" + req.cookies.userinfo.username + name + ".txt"
        ),
        "正确答案" + arr2[j] + "\n"
      );
    }
    let address1 = req.cookies.userinfo.username + name + ".txt";
    let myDate1 = new Date();
    pool.query(
      "insert into file (filename,score,time,name) values (?,?,?,?)",
      [address1, req.body.score, myDate1, req.cookies.userinfo.username],
      (err, result) => {
        //err可能产生的错误,如果出现错误
        if (err) {
          next(err); //抛给下一个中间件来处理
          //阻止往后执行
          return;
        }
        console.log("成功1");
      }
    );
    pool.releaseConnection(pool);
  }

  res.send({
    status: 200,
    msg: "接收成功",
  });
});

//下载文件
r.get("/download", (req, res) => {
  //查询当前用户的文件
  pool.query(
    "select * from file where name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      //console.log(result);
      //结果是数组，如果是空数组，提示查询失败
      if (result.length == 0) {
        console.log("查询失败，没有该用户文件");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//路由传参下载文件
r.get("/download/:name", (req, res) => {
  console.log(req.params.name);
  res.download(path.join(__dirname, "../public/score/" + req.params.name));
});

//判断该场考试是否未开始，或已经考试
r.get("/test/:eid", (req, res, next) => {
  pool.query(
    "select school,faculty,major,classroom from studentinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("查询失败，没有班级信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        //console.log(req.cookies.userinfo)
        //查找考试信息表中的所有信息，条件是考试信息表中eid不在学生考试信息表中
        //select distinct examinfo.eid from examinfo where examinfo.eid not in (select eid from studentexaminfo)
        pool.query(
          "select distinct examinfo.* from examinfo where school=? and faculty=? and major=? and classroom=? and examinfo.eid not in (select eid from studentexaminfo where school=? and faculty=? and major=? and classroom=?)",
          [
            result[0].school,
            result[0].faculty,
            result[0].major,
            result[0].classroom,
            result[0].school,
            result[0].faculty,
            result[0].major,
            result[0].classroom,
          ],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result)

            if (result.length == 0) {
              res.send({ msg: 200 });
            } else {
              var arr1 = [];
              var arr2 = []; //因为数据库操作是异步的它是在赋值后执行的，所以在外面打印会是空数组
              for (let a = 0; a < result.length; a++) {
                let new_date = new Date(); //当前时间
                var stime = new Date(result[a].start_time); //开始时间
                var etime = new Date(result[a].end_time); //结束时间
                if (new_date - etime >= 0) {
                  //如果当前时间减去结束时间大于0则表示这次考试已结束
                  arr1.push(result[a].eid);
                  // console.log(`arr1:${arr1}`)
                } else if (new_date - stime <= 0) {
                  //如果当前时间减去开始时间小于0则表示这次考试未开始
                  arr2.push(result[a].eid);
                  // console.log(`arr2:${arr2}`)
                }
              }
              // console.log(arr1.indexOf(`${req.params.eid}`))
              if (arr1.indexOf(`${req.params.eid}`) != -1) {
                res.send({ msg: "考试已结束" });
              } else if (arr2.indexOf(`${req.params.eid}`) != -1) {
                res.send({ msg: "考试未开始" });
              } else {
                //查询学生完成考试数据表,如果某学生参加这次考试则禁止他再访问
                pool.query(
                  "select * from studentexaminfo where eid=? and u_name=?",
                  [req.params.eid, req.cookies.userinfo.username],
                  (err, result) => {
                    if (err) {
                      next(err);
                      return;
                    }

                    if (result.length == 0) {
                      res.send({ msg: 200 });
                    } else {
                      var arr3 = [];
                      for (let a = 0; a < result.length; a++) {
                        arr3.push(result[a].eid);
                      }
                      if (arr3.indexOf(`${req.params.eid}`) != -1) {
                        res.send({ msg: "你已考试" });
                        //如果有这个考试的id则发送已考试页面，然后阻止代码向下执行，避免再发生考试界面文件造成重复发送报错
                        return;
                      }
                    }
                    res.send({ msg: 200 });
                  }
                );
              }
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//获取发布的试卷信息，通过学生所在学校及院系及专业班级来查找该班级是否有考试
r.get("/examinfo", (req, res, next) => {
  //先从学生信息表中查询到班级
  pool.query(
    "select school,faculty,major,classroom from studentinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("查询失败，没有班级信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select * from examinfo where school=? and faculty=? and major=? and classroom=?",
          [
            result[0].school,
            result[0].faculty,
            result[0].major,
            result[0].classroom,
          ],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败,该班级不存在");
              res.send({
                status: 201,
                msg: "查询失败",
              });
            } else {
              res.send({
                status: 200,
                msg: "查询成功",
                data: result,
              });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//通过学校，院系，eid来找对应的试卷题目
r.get("/exam/:eid", (req, res, next) => {
  pool.query(
    "select * from studentinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败，没有该用户信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select e_sj from exam where eid=? and school=? and faculty=?",
          [req.params.eid, result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败，没有该eid");
              res.send({
                status: 201,
                msg: "查询失败",
              });
            } else {
              res.send({
                status: 200,
                msg: "查询成功",
                data: result,
              });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//提交学生考试信息
r.post("/studentexaminfo", (req, res, next) => {
  // console.log(req.body);
  //先查询学生信息
  pool.query(
    "select classroom,name,studentid,school,faculty,major from studentinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("学生信息表没有该班级和姓名");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        let school = result[0].school;
        let faculty = result[0].faculty;
        let major = result[0].major;
        let classroom = result[0].classroom;
        let name = result[0].name;
        let studentid = result[0].studentid;
        //查询考试名称
        pool.query(
          "select e_name from examinfo where school=? and faculty=? and major=? and classroom=? and eid=?",
          [result[0].school,result[0].faculty,result[0].major,result[0].classroom,req.body.eid],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("学生考试信息表没有该eid");
              res.send({
                status: 201,
                msg: "查询失败",
              });
            } else {
              // console.log(result)
              let ename = result[0].e_name;
              console.log(ename)
              let time = new Date();
              // console.log(req.body.studentAnswer);
              pool.query(
                "insert into studentexaminfo (u_name,e_name,eid,score,time,spendtime,answer,classroom,name,studentid,school,faculty,major) values(?,?,?,?,?,?,?,?,?,?,?,?,?)",
                [
                  req.cookies.userinfo.username,
                  ename,
                  req.body.eid,
                  req.body.score,
                  time,
                  req.body.str,
                  req.body.studentAnswer,
                  classroom,
                  name,
                  studentid,
                  school,
                  faculty,
                  major,
                ],
                (err, result) => {
                  if (err) {
                    next(err);
                    return;
                  }
                  res.send({
                    status: 200,
                    msg: "插入成功",
                  });
                }
              );
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//查询学生考试信息
r.get("/studentexaminfo", (req, res, next) => {
  //通过用户名
  pool.query(
    "select * from studentexaminfo where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("没有该学生试卷数据");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询学生考试的答案
r.get("/studentexaminfo/:eid", (req, res, next) => {
  pool.query(
    "select * from studentinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败，没有该用户信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select answer from studentexaminfo where u_name=? and school=? and faculty=? and major=? and classroom=? and eid=?",
          [
            req.cookies.userinfo.username,
            result[0].school,
            result[0].faculty,
            result[0].major,
            result[0].classroom,
            req.params.eid,
          ],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("没有该学生答案数据");
              res.send({
                status: 201,
                msg: "查询失败",
              });
            } else {
              res.send({
                status: 200,
                msg: "查询成功",
                data: result[0],
              });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//查询学校
r.get("/schoolinfo", (req, res, next) => {
  pool.query("select distinct school from schoolinfo", (err, result) => {
    if (err) {
      next(err);
      return;
    }
    // console.log(result);
    if (result.length == 0) {
      console.log("没有该学校");
      res.send({
        status: 201,
        msg: "查询失败",
      });
    } else {
      res.send({
        status: 200,
        msg: "查询成功",
        data: result,
      });
    }
  });
  pool.releaseConnection(pool);
});

//查询学校对应院系
r.get("/schoolinfo/:school", (req, res, next) => {
  pool.query(
    "select distinct faculty from schoolinfo where school=?",
    [req.params.school],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("没有该院系");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询学校对应院系对应专业
r.get("/schoolinfo2/:school&:faculty", (req, res, next) => {
  // console.log(req.params.school, req.params.faculty);
  pool.query(
    "select distinct major from schoolinfo where school=? and faculty=?",
    [req.params.school, req.params.faculty],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("没有该专业");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询学校对应院系对应班级
r.get("/selectclassroom/:school&:faculty&:major", (req, res, next) => {
  pool.query(
    "select distinct classroom from classroom where school=? and faculty=? and major=?",
    [req.params.school, req.params.faculty, req.params.major],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有班级信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//提交学生信息，先把user表的信息拿出来,把user表的信息插入
r.post("/studentinfomation", (req, res, next) => {
  pool.query(
    "select * from user where username=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("该用户信息为空");
      } else {
        pool.query(
          "insert into studentinfomation (u_name,phone,sex,birthday,email,classroom,name,studentid,school,faculty,major) values(?,?,?,?,?,?,?,?,?,?,?)",
          [
            result[0].username,
            result[0].phone,
            result[0].sex,
            result[0].birthday,
            result[0].email,
            req.body.classroom,
            req.body.name,
            req.body.studentid,
            req.body.school,
            req.body.faculty,
            req.body.major,
          ],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }

            res.send({
              status: 200,
              msg: "插入成功",
            });
          }
        );
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询学生信息
r.get("/studentinfomation", (req, res, next) => {
  pool.query(
    "select * from studentinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有该学生数据");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//修改学生信息
r.put("/studentinfomation", (req, res, next) => {
  // console.log(req.body)
  //修改学信息标同时也应该修改user表对应的数据(为避免信息表信息已修改而user表信息未修改的情况，要先修改user表)
  pool.query(
    "update user set phone=?,sex=?,birthday=?,email=? where username=?",
    [
      req.body.phone,
      req.body.sex,
      req.body.birthday,
      req.body.email,
      req.cookies.userinfo.username,
    ],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      //还要修改studentexaminfo表的数据
      pool.query(
        "update studentexaminfo set classroom=?,name=?,studentid=?,school=?,faculty=?,major=? where u_name=?",
        [
          req.body.classroom,
          req.body.name,
          req.body.studentid,
          req.body.school,
          req.body.faculty,
          req.body.major,
          req.cookies.userinfo.username,
        ],
        (err, result) => {
          if (err) {
            next(err);
            return;
          }

          //update 更新多个字段时不能用and 只能用,号隔开
          pool.query(
            "update studentinfomation set phone=?,sex=?,birthday=?,email=?,classroom=?,name=?,studentid=?,school=?,faculty=?,major=? where u_name=?",
            [
              req.body.phone,
              req.body.sex,
              req.body.birthday,
              req.body.email,
              req.body.classroom,
              req.body.name,
              req.body.studentid,
              req.body.school,
              req.body.faculty,
              req.body.major,
              req.cookies.userinfo.username,
            ],
            (err, result) => {
              if (err) {
                next(err);
                return;
              }

              if (result.affectedRows == 0) {
                res.send({
                  status: 201,
                  msg: "修改失败",
                });
              } else {
                res.send({
                  status: 200,
                  msg: "修改成功",
                });
              }
            }
          );
        }
      );
    }
  );

  pool.releaseConnection(pool);
});

//删除学生信息
r.delete("/studentinfomation", (req, res, next) => {
  pool.query(
    "delete from studentinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      if (result.affectedRows == 0) {
        res.send({
          status: 201,
          msg: "删除学生信息失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "删除成功",
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//导出路由器对象
module.exports = r;
